 use newBiz
 GO


IF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = 'Code')
	BEGIN
		DROP  Table [dbo].[Code]
	END
GO

CREATE TABLE [dbo].[Code](
	[CodeID] [varchar](20) NOT NULL,
    [CodeDesc] [varchar](100) NOT NULL,
    [CodeType] [varchar](20) NOT NULL
) ON [PRIMARY]
GO


IF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = 'Users')
	BEGIN
		DROP  Table [dbo].[Users]
	END
GO

CREATE TABLE [dbo].[Users](
	[Id] [varchar](50) default newid(),
	[UserName] [varchar](50) NOT NULL,
    [UserPassword] [varchar](50) NOT NULL,
    [UserType] [varchar](20) NULL,
    [isAdmin] bit default 0,
    LastLog datetime default getdate()
) ON [PRIMARY]
GO

insert into users(username, [UserPassword])
select 'admin','123'
go


IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'user_LogIn')
	BEGIN
		DROP  procedure [dbo].user_LogIn
	END
GO

create procedure user_LogIn
   @username varchar(50),
   @pass varchar(50)
AS
  if exists(select * from users where  [UserName] = @username and [UserPassword] = @pass)
    begin
       update users set LastLog = getdate() where [UserName] = @username and [UserPassword] = @pass
       select * from users where  [UserName] = @username and [UserPassword] = @pass
    end
GO  

IF EXISTS (SELECT * FROM sysobjects WHERE   name = 'Member')
	BEGIN
		DROP  table [dbo].[Member]
	END
GO

CREATE TABLE [dbo].[Member](
	[Id] [varchar](50) default newid(),
	[MemberID]  [varchar](20)  NOT NULL,
	[MemberPlan] [varchar](20) NULL,
	[Category] [varchar](20) NULL,
	[AgentPlan] [varchar](20)  NULL,
	[MemberPosition] [varchar](20) NULL,
    [LastName] [varchar](100) NULL,
	[FirstName] [varchar](100) NULL,
	[MiddleName] [varchar](100) NULL,
	[Agent] [varchar](20) NULL,
	[BusinessCenter] [varchar](100) NULL,
	[RegionalManager] [varchar](100) NULL,
	[DateRegistered] datetime default getdate() NOT NULL,
	[DateProcess] datetime NULL,
    [BirthDate] datetime null,
	[BirthPlace] [varchar](200) NULL,
	[Gender] [char](1) NULL,	
	[CivilStatus] [varchar](20) NULL,	
	[Religion] [varchar](200) NULL,
	[Address1] [varchar](200) NULL,
	[Address2] [varchar](200) NULL,
	[AddressCity] [varchar](200) NULL,
	[TelNo] [varchar](100) NULL,
	[MobileNo] [varchar](100) NULL,
	[Occupation] [varchar](200) NULL,
	[TIN] [varchar](100) NULL,
	[Email] [varchar](200) NULL
) ON [PRIMARY]

GO


IF EXISTS (SELECT * FROM sysobjects WHERE   name = 'MemberApproval')
	BEGIN
		DROP  table [dbo].[MemberApproval]
	END
GO

CREATE TABLE [dbo].[MemberApproval](
    [Id] [int] IDENTITY(1,1) NOT NULL,
	[MemberID] [varchar](20) not NULL,
	Coverage_Start datetime default getdate() null,
	Coverage_End datetime default getdate() null,
	Enrollment_No  [varchar](100) NULL,
	Memorial_Service  [varchar](100) NULL,
	Service_Value [varchar](20) NULL,
	Servicing_Mortuary     [varchar](100) NULL,
	MSA_Hotline  [varchar](100) NULL,
	Cash_Assistance  [varchar](20) NULL,
	Accidental_Death_Benefit  [varchar](20) NULL,
	Remarks  [varchar](max) NULL,
	Active bit default 1 NULL
) ON [PRIMARY]

IF EXISTS (SELECT * FROM sysobjects WHERE   name = 'get_member')
	BEGIN
		DROP  procedure [dbo].get_member
	END
GO

create procedure  get_member
   @txtsearch varchar(100)
AS
  declare @txt varchar(100)
  set @txt = isnull(@txtsearch,'')
  
  select * from member where 
     memberID like '%' + @txt + '%'
     or lastname like '%' + @txt + '%'
     or firstname like '%' + @txt + '%'
     or middlename like '%' + @txt + '%'
GO  
 
IF EXISTS (SELECT * FROM sysobjects WHERE   name = 'get_memberAgent')
	BEGIN
		DROP  procedure [dbo].get_memberAgent
	END
GO


create procedure  get_memberAgent
   @txtsearch varchar(100)
AS
  declare @txt varchar(100)
  set @txt = isnull(@txtsearch,'')
  
  select * from member where 
    category = 'Agent'
    and
       ( memberID like '%' + @txt + '%'
     or lastname like '%' + @txt + '%'
     or firstname like '%' + @txt + '%'
     or middlename like '%' + @txt + '%')
GO  

IF EXISTS (SELECT * FROM sysobjects WHERE   name = 'get_memberClient')
	BEGIN
		DROP  procedure [dbo].get_memberClient
	END
GO

create procedure  get_memberClient
   @txtsearch varchar(100)
AS
  declare @txt varchar(100)
  set @txt = isnull(@txtsearch,'')
  
  select * from member where 
    category = 'Client'
    and
       ( memberID like '%' + @txt + '%'
     or lastname like '%' + @txt + '%'
     or firstname like '%' + @txt + '%'
     or middlename like '%' + @txt + '%')
GO  
  

IF EXISTS (SELECT * FROM sysobjects WHERE   name = 'get_memberInfo')
	BEGIN
		DROP  procedure [dbo].get_memberInfo
	END
GO

create procedure get_memberInfo
   @id varchar(20)
AS
     select * from member where MemberID = @id
GO   


IF EXISTS (SELECT * FROM sysobjects WHERE   name = 'PlanAmount')
	BEGIN
		DROP  table [dbo].PlanAmount
	END
GO
 
 
CREATE TABLE [dbo].[PlanAmount](
	[MemberPlan] [varchar](20) NULL,
	[AgentPlan] [varchar](20) NULL,
	[Level] int default 0 NULL,
	[Amount] money default 0 null
) ON [PRIMARY] 



/*
CREATE TABLE [dbo].[Approval_History](
	[MemberID]  [varchar](20)  NOT NULL,
	Coverage_Start datetime null,
	Coverage_End datetime null,
	Enrollment_No 
	Memorial Service
	Service_Value
	Servicing Mortuary   
	MSA Hotline
	Cash Assistance
	Accidental Death Benefit
	Remarks
) ON [PRIMARY]

Geneology

Direct Referral Earnings

Unilevel Earnings

CREATE TABLE [dbo].[UserLevel](
	[UserId] [int] NOT NULL,
	[RecuitBy] [int] NOT NULL,
    [RecruitDate] datetime NOT NULL
) ON [PRIMARY]


CREATE TABLE [dbo].[Beneficiary](
    [Id] [int] IDENTITY(1,1) NOT NULL,
	[UserId] [int] NOT NULL,
    [LastName] [varchar](100) NULL,
	[FirstName] [varchar](100) NULL,
	[MiddleName] [varchar](100) NULL,	
	[BirthDate] datetime,
	[Gender] [char](1) NULL,		
	[Relationship] [varchar] (200) NULL
) ON [PRIMARY]


CREATE TABLE [dbo].[UserInfo](
	[UserId] [int] NOT NULL,
	[LastName] [varchar](100) NULL,
	[FirstName] [varchar](100) NULL,
	[MiddleName] [varchar](100) NULL,	
	[BirthDate] datetime,
	[BirthPlace] [varchar](200) NULL,
	[Gender] [char](1) NULL,	
	[CivilStatus] [char](2) NULL,	
	[Religion] [varchar](200) NULL,
	[Address1] [varchar](200) NULL,
	[Address2] [varchar](200) NULL,
	[AddressCity] [varchar](200) NULL,
	[TelNo] [varchar](100) NULL,
	[MobileNo] [varchar](100) NULL,
	[Occupation] [varchar](200) NULL,
	[TIN] [varchar](100) NULL,
	[Email] [varchar](200) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[Sponsor](
	[UserId] [int] NOT NULL,
	[RecuitBy] [int] NOT NULL,
    [BC] [varchar](200) NULL
) ON [PRIMARY]



GO
*/

/* new

*/

IF EXISTS (SELECT * FROM sysobjects WHERE   name = 'PlanAmount')
	BEGIN
		DROP  table [dbo].PlanAmount
	END
GO
 
 
CREATE TABLE [dbo].PlanAmount(
	[MemberPlan] [varchar](20) NULL,
	[AgentPlan] [varchar](20) NULL,
	[Level] int default 0 NULL,
	[Amount] money default 0 null
) ON [PRIMARY] 


GO

IF EXISTS (SELECT * FROM sysobjects WHERE   name = 'MemberLevel')
	BEGIN
		DROP  table [dbo].[MemberLevel]
	END
GO
 

CREATE TABLE [dbo].[MemberLevel](
	[MemberID]  [varchar](20)  NOT NULL,
	[AgentID]  [varchar](20)  NOT NULL,
	[MemberPlan] [varchar](20) NULL,
	[Level] int default 0 NULL,
	[CLevel] int default 0 NULL,
	[Amount] money default 0 null,
	[DateRegistered] datetime default getdate() NULL,
    [DateProcess] datetime NULL
) ON [PRIMARY]

IF EXISTS (SELECT * FROM sysobjects WHERE   name = 'vwMemberLevel')
	BEGIN
		DROP  view [dbo].vwMemberLevel
	END
GO 

Create View vwMemberLevel 
AS
select ml.memberid,
m.LastName + ', ' + m.FirstName + ' ' + m.MiddleName +
case len(m.MiddleName)  
when 1 then '.' else '' end as MemberName,
ml.AgentID,
s.LastName + ', ' + s.FirstName + ' ' + s.MiddleName +
case len(s.MiddleName)  
when 1 then '.' else '' end as Agent,
 ml.MemberPlan ,ml.level, ml.CLevel, ml.Amount, ml.DateRegistered, ml.DateProcess
 from memberlevel ml
left outer join Member m on ml.MemberID = m.MemberID
left outer join Member s on ml.AgentID = s.MemberID
GO

IF EXISTS (SELECT * FROM sysobjects WHERE   name = 'get_DirectEarning')
	BEGIN
		DROP  procedure [dbo].get_DirectEarning
	END
GO 

  create procedure get_DirectEarning
   @id varchar(20)
AS
     select * from vwMemberLevel where [AgentId] = @id and CLevel = 1
    order by DateRegistered
GO


IF EXISTS (SELECT * FROM sysobjects WHERE   name = 'get_UniLevelEarning')
	BEGIN
		DROP  procedure [dbo].get_UniLevelEarning
	END
GO 

 

create procedure get_UniLevelEarning
   @id varchar(20)
AS
      select * from vwMemberLevel where [AgentId] = @id and CLevel > 1
    order by DateRegistered
GO 


IF EXISTS (SELECT * FROM sysobjects WHERE   name = 'get_DirectEarningSummary')
	BEGIN
		DROP  procedure [dbo].get_DirectEarningSummary
	END
GO 

create procedure get_DirectEarningSummary
   @dtFrom datetime,
   @dtTo datetime
AS
     SELECT        AgentID, Agent, SUM(Amount) AS Expr1
FROM            vwMemberLevel
GROUP BY AgentID, Agent, CLevel
HAVING        (CLevel = 1)
     
     
GO

IF EXISTS (SELECT * FROM sysobjects WHERE   name = 'get_UnilevelSummary')
	BEGIN
		DROP  procedure [dbo].get_UnilevelSummary
	END
GO 

create procedure get_UnilevelSummary
   @dtFrom datetime,
   @dtTo datetime
AS
     SELECT        AgentID, Agent, SUM(Amount) AS Expr1
FROM            vwMemberLevel
GROUP BY AgentID, Agent, CLevel
HAVING        (CLevel = 1)
     
     
GO


IF EXISTS (SELECT * FROM sysobjects WHERE   name = 'get_forProcess')
	BEGIN
		DROP  procedure [dbo].get_forProcess
	END
GO 

create procedure get_forProcess
   @dtFrom datetime,
   @dtTo datetime
AS
     select top 6 * from vwMemberLevel
     
     
GO
 

 
 

 IF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = 'MemberCLevel')
	BEGIN
		DROP  Table [dbo].[MemberCLevel]
	END
GO

CREATE TABLE [dbo].[MemberCLevel](
	[MemberID]  [varchar](20)  NOT NULL,
	[AgentID]  [varchar](20)  NOT NULL,
	[MemberPlan] [varchar](20) NULL,
	[Level] int default 0 NULL,
	[CLevel] int default 0 NULL
) ON [PRIMARY]
GO



 IF EXISTS (SELECT * FROM sysobjects WHERE   name = 'Process_Member')
	BEGIN
		DROP  procedure [dbo].Process_Member
	END
GO 

create procedure Process_Member
    @id varchar(20)
AS
		declare @date datetime
		select @date = DateRegistered from Member where memberid = @id

		-- Get agent with sales
		--select distinct agent from Member
		--where Agent in
		--(select agent from MemberLevel where memberid = @id)
		--and month(DateRegistered) = month(@date)
		--and year(DateRegistered) = year(@date)

		delete from MemberCLevel where memberid = @id

		insert into MemberCLevel(CLevel, MemberID, AgentID, [Level])
		SELECT ROW_NUMBER() OVER(ORDER BY level) AS cLevel,@id, agentid, level
		from MemberLevel where MemberID = @id 
		and AgentID in 
				(select distinct agent from Member -- get agents with sales
				where Agent in
				(select agent from MemberLevel where memberid = @id)
				and month(DateRegistered) = month(@date)
				and year(DateRegistered) = year(@date))
       
	   update MemberLevel set CLevel = 0 where MemberID = @id

        -- update memberlevel from memberclevel
		--update member
GO
